package com.sihan.framework.etm.common.util;

import java.io.File;
import java.io.FileInputStream;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import lombok.extern.slf4j.Slf4j;

@Slf4j
public class ExcelUtils {
	
	/**
	 * @param sheet  模板
	 * @param fromLine
	 * @param columnValue 数据
	 * @throws IllegalArgumentException
	 * @throws IllegalAccessException
	 */
	public static void exportExcelFileFromTemplate(Sheet sheet,
			List<?> columnValue) throws IllegalArgumentException, IllegalAccessException {
		
		Row row = null;
		Iterator<?> it = columnValue.iterator();
		int index = 0;
		while (it.hasNext()) {
			index++;
			row = sheet.getRow(index);
			Object t = it.next();
			Class<?> cls = t.getClass();
			Field[] fields = cls.getDeclaredFields();
			int cellNo = 0;
			for (Field field : fields) {
				field.setAccessible(true);
				Object object = field.get(t);
				String textValue = null;
				Cell cell = row.getCell(cellNo);
				if (object instanceof Date) {
					Date date = (Date) object;
					SimpleDateFormat sdf = new SimpleDateFormat(
							"yyyy-MM-dd hh:mm:ss");
					if(null != object){
						textValue = sdf.format(date);
					}
				} else {
					if(null != object){
						textValue = object.toString();
					}
				}

				if (textValue != null) {
					Pattern p = Pattern.compile("^//d+(//.//d+)?$");
					Matcher matcher = p.matcher(textValue);
					if (matcher.matches() && textValue.length() < 15) {
						cell.setCellValue(Double.parseDouble(textValue));
					} else {
						XSSFRichTextString richString = null;
						try {
							 richString = new XSSFRichTextString(
									textValue);
							cell.setCellValue(richString);
						} catch (Exception e) {
							log.error("导出excel异常",e);
						}
					}
				}else {
					cell.setCellValue("");
				}

				cellNo++;
			}
		}
	}
	
	/**
	 * 读取excel数据
	 * @param file
	 * @param colList
	 * @return
	 * @throws Exception
	 */
	public static Map<String, Object> parseExcel2007(File file,
			List<String> colList) throws Exception {
		Map<String, Object> map = new HashMap<String, Object>();
		Map<String, Integer> headerMap = new HashMap<String, Integer>();
		XSSFWorkbook workBook = new XSSFWorkbook(new FileInputStream(file));
		XSSFSheet sheet = workBook.getSheetAt(0);
		int firstRowNum = sheet.getFirstRowNum();
		int totalRowNum = sheet.getLastRowNum();
		XSSFRow row = null;
		XSSFCell cell = null;
		int k = 0;
		// 读取表头行(第一个非空行为表头行)
		for (int i = firstRowNum; i <= totalRowNum; i++) {
			row = sheet.getRow(i);
			if (row != null) {
				headerMap = readSheetHeader2007(row, colList);
				k = i;
				break;
			}
		}
		// 读取指定列的数据
		if (headerMap != null && headerMap.size() > 0) {
			DecimalFormat df = new DecimalFormat("#");
			Iterator<String> it = headerMap.keySet().iterator();
			while (it.hasNext()) {
				String key = it.next();
				int colNum = headerMap.get(key);
				List<Object> list = new ArrayList<Object>();
				int i = k + 1;
				// 从表头的下一行开始读取数据
				while (i <= totalRowNum) {
					Object cellValue = null;
					String value = null;
					row = sheet.getRow(i);
					if (row != null) {
						cell = row.getCell(colNum);
						if (cell != null) {
							switch (cell.getCellTypeEnum()) {
							case STRING:
								cellValue = cell.getStringCellValue();
								break;
							case NUMERIC:
								if (HSSFDateUtil.isCellDateFormatted(cell)) {
									Date date = cell.getDateCellValue();
									cellValue = DateFormatUtils.format(date, "yyyy-MM-dd");
								} else {
									cellValue = df.format(cell.getNumericCellValue());
								}
								break;
							case BLANK:
								cellValue = null;
								break;
							default:
								cellValue = null;
								break;
							}
						}
						if (cellValue != null) {
							value = cellValue.toString();
						}
					}
					list.add(value);
					i++;
				}
				if (i > totalRowNum) {
					map.put(key, list);
				}
			}
		}
		workBook.close();
		return map;
	}
	
	private static Map<String, Integer> readSheetHeader2007(XSSFRow row,
			List<String> colList) {
		Map<String, Integer> headerMap = new HashMap<String, Integer>();
		if (colList != null && colList.size() > 0) {
			int firstCellNum = row.getFirstCellNum();
			int lastCellNum = row.getLastCellNum();
			// 获取指定列名所在列号
			for (int j = firstCellNum; j < lastCellNum; j++) {
				XSSFCell cell = row.getCell(j);
				if (cell != null) {
					String cellValue = cell.getStringCellValue();
					for (String colName : colList) {
						if ((cellValue.equals(colName))
								&& !headerMap.containsKey(colName)) {
							headerMap.put(colName, j);
						}
					}
				}
			}
		}

		return headerMap;
	}
	
	/**
	 * 
	 * @description 创建excel
	 * @param writerSheet
	 * @param content
	 * @param columnIndex
	 * @param rowIndex
	 * @param merge
	 * @param fromColumn
	 * @param endColumn
	 * @param fromRow
	 * @param endRow
	 * @param cellStyle
	 * @auther 韩兵
	 * @Date 2020年5月21日
	 * @return void
	 */
	public static void createExcelBySelf(Sheet sheet,int rowHeight,Object content,int rowIndex, int columnIndex, 
			boolean merge,int fromRow,int endRow,int fromCol,int endCol,CellStyle cellStyle) {
		Row row = sheet.getRow(rowIndex);
		if(null == row) {
			row = sheet.createRow(rowIndex);
		}
		row.setHeightInPoints(rowHeight);
		Cell cell = row.createCell(columnIndex);
		if(merge) {
			for(int i=fromRow;i<endRow;i++) {
				Row innerRow = sheet.getRow(i);
				if(null == innerRow) {
					innerRow = sheet.createRow(i);
				}
				for(int j=fromCol;j<endCol;j++) {
					cell = innerRow.createCell(j);
					cell.setCellStyle(cellStyle);
				}
			}
			
			CellRangeAddress region = new CellRangeAddress(fromRow, endRow, fromCol, endCol);
			sheet.addMergedRegion(region);
		}
		
		if(content instanceof String) {
			cell.setCellValue((String)content);
		}else if(content instanceof Double){
			cell.setCellValue((Double)content);
		}
		cell.setCellStyle(cellStyle);
		
	}
	
	public static CellStyle setCellStyle(HSSFWorkbook book,short color) {
		CellStyle cellStyle = book.createCellStyle();
		Font font = book.createFont();
		font.setFontName("宋体");
		font.setFontHeightInPoints((short)12);
		cellStyle.setFont(font);
		cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); 
		cellStyle.setFillForegroundColor(color);//IndexedColors.YELLOW.index
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
	    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
	    
	    cellStyle.setBorderBottom(BorderStyle.THIN);
	    cellStyle.setBorderTop(BorderStyle.THIN);
	    cellStyle.setBorderLeft(BorderStyle.THIN);
	    cellStyle.setBorderRight(BorderStyle.THIN);

		return cellStyle;
	}
}
